---
id: orders-db
name: Orders DB
version: 0.0.1
container_type: database
technology: postgres@14
authoritative: true
access_mode: readWrite
purpose: System of record for Orders and OrderLines
classification: internal
retention: 7y
residency: eu-west-1
---

<NodeGraph />

### What is this?
Orders DB is the primary database for the Orders domain. It is a PostgreSQL 14 database and acts as the system of record for orders and their line items. If you are looking for where an order lives, how to query it, or how order state changes are persisted, you are in the right place.

### What does it store?
- **Orders**: One row per customer order. Includes status, totals, currency, timestamps.
- **Order Lines**: One row per item inside an order. Includes product, quantities, and pricing.
- **Relationships**: `order_lines` are linked to `orders` by `order_id` (cascading deletes are enabled).

## Schema and test data for Orders DB
<AccordionGroup>
  <Accordion title="Orders DB Schema">
    <Schema file="db.sql" lang="sql" title="Orders DB Schema.sql" />
  </Accordion>
  <Accordion title="Test data for Orders DB">
    <Schema file="test-data.sql" lang="sql" title="Test data for Orders DB.sql" />
  </Accordion>
</AccordionGroup>

### High-level data model
- An `order` has many `order_lines`.
- Order lifecycle is tracked via the `status` column. Common statuses: `PENDING`, `PAID`, `CANCELLED`, `FULFILLED`, `REFUNDED`.
- Monetary values are stored as integer cents; currency is 3-letter ISO (e.g. `USD`).


### Common queries
```sql
-- Fetch a single order with its lines
SELECT o.*, l.*
FROM orders o
LEFT JOIN order_lines l USING (order_id)
WHERE o.order_id = $1;

-- List recent orders for a customer (paged)
SELECT *
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT $2 OFFSET $3;

-- Daily revenue (rounded to dollars) for the last 30 days
SELECT date_trunc('day', created_at) AS day,
       SUM(total_amount_cents)/100.0 AS revenue
FROM orders
WHERE created_at >= now() - interval '30 days'
  AND status IN ('PAID','FULFILLED','REFUNDED')
GROUP BY 1
ORDER BY 1;
```

### How data gets here (lifecycle)
1. A user places an order → `OrdersService` validates input and writes to `orders` and `order_lines` in a single transaction.
2. Payment success updates the order `status` to `PAID`.
3. Fulfillment updates the order `status` to `FULFILLED`.
4. Cancellations and refunds update `status` accordingly.
5. Each change emits a domain event that other services consume.

### Access patterns and guidance
- Use the `order_id` for point lookups; most queries should be indexed by this or `customer_id`.
- Prefer reading from read replicas for analytics/reporting workloads when available.
- Treat `status` as the source of truth for order lifecycle; avoid inferring state from timestamps alone.
- Do not store PII beyond `customer_id` here; card data never resides in this database.

### Retention and residency
- Retention: 7 years (see frontmatter). Archival or partitioning may be used to keep hot data smaller.
- Residency: `eu-west-1` (see frontmatter). Ensure cross-region access complies with data policies.

### Backups and recovery
- Automated daily snapshots, plus point-in-time recovery retained per platform policy.
- Test restores should be performed regularly to validate RPO/RTO.

### Security
- Access is role-based. `OrdersService` has read/write. Downstream services typically have read-only.
- Enforce least privilege at the database role level. Avoid ad-hoc superuser connections.

### Operational notes
- Consider time-based partitioning on `orders.created_at` for very large datasets.
- Monitor bloat and autovacuum; ensure `vacuum_analyze` runs regularly.
- Keep indexes targeted; avoid over-indexing write-heavy columns.

### Local development
- Connection string: provided via `ORDERS_DB_URL` environment variable.
- Seed data: use project seed scripts to create sample orders and lines.

### Gotchas
- Deleting an order cascades to `order_lines` due to `ON DELETE CASCADE`. Prefer status changes over deletes in most cases.
- Monetary math should be done in integer cents to avoid float rounding issues.

If you’re unsure which table to use or how to fetch something, start with the `orders` table by `order_id`, then join `order_lines` as needed. For more, see the `OrdersService` documentation and related domain events.